﻿/* -----------------------------------------------------------------------
 * <copyright file="DataProvider.cs" company="O-RID">
 *     Copyright (c)2010 O-RID Co.,Ltd.
 * </copyright>
 * <author>Zhang Jun</author>
 * <summary>
 *          WCFAccounts.svc服务实现的具体方法
 *          Get（Set）+服务方法名
 *          作成日：05/21/2010
 *                  08/03/2010 增加 取客户端IP地址
 *                  08/27/2010 增加 查询SV、SSV 对应的组别信息，或是查询某组由那些SV来管理 信息
 *                  09/06/2010 增加 设置TreeView权限树
 *                  09/07/2010 增加 显示所有的Roles，对Roles进行 增删改
 *                  09/19/2010 增加 【新增用户子窗体】 查询已有用户
 *                  09/20/2010 增加 【新增用户子窗体】 添加新增用户
 *                  10/25/2010 增加 【SV管理页面】 查找已/未分组的SV或SSV信息
 *                  10/28/2010 -- 10/29/2010 
 *                             增加 【SV管理页面】添加组信息、删除组信息
 * </summary>
 * -----------------------------------------------------------------------
 */
namespace Orid.Management.Statistics.Service.OPUnitService.Account
{
    using System;
    using System.Collections.Generic;
    using System.Configuration;
    using System.Data;
    using System.Data.SqlClient;
    using System.ServiceModel;
    using System.ServiceModel.Channels;
    using System.Text;
    using Orid.Management.Statistics.Service.OPUnitService.Account.ServiceModel;

    /// <summary>
    /// DataProvider
    /// WCFAccounts.svc服务实现的具体方法
    /// </summary>
    public class DataProvider
    {
        /// <summary>
        /// SystemNameStr
        /// 取得Web.config的SystemName值
        /// </summary>
        private static string systemNameStr = ConfigurationManager.ConnectionStrings["SystemName"].ConnectionString.ToString().Trim();

        #region  验证登录
        /// <summary>
        /// 验证登录
        /// WCFAccounts服务用
        /// </summary>
        /// <param name="loginName">loginName 用户ID</param>
        /// <param name="passWord">passWord 用户密码</param>
        /// <returns>返回 LoginDataList</returns>
        public static LoginDataList VerifyAccount(string loginName, string passWord)
        {
            LoginDataList login = new LoginDataList();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StatisticsToolsDB"].ConnectionString))
            {
                conn.Open();

                StringBuilder sqlStringBuilder = new StringBuilder();

                sqlStringBuilder.Append(" SELECT AccountOfStastic.AccountID,OpName,ParentID,AccountState");
                sqlStringBuilder.Append(" FROM AccountOfStastic");
                sqlStringBuilder.Append(" INNER JOIN dbo.Accounts ON");
                sqlStringBuilder.Append(" AccountOfStastic.AccountID = Accounts.LoginName");
                sqlStringBuilder.Append(" LEFT JOIN ( SELECT DISTINCT OpID, OpName FROM  dbo.SVInfo ) svinfo ON");
                sqlStringBuilder.Append(" Accounts.LoginName  = SVInfo.opid");
                sqlStringBuilder.Append(" WHERE AccountOfStastic.AccountID =@LoginName AND Password = @Password");
                sqlStringBuilder.Append(" AND AccountOfStastic.System = @SystemName");
                sqlStringBuilder.Append(" AND AccountOfStastic.AccountState= '1'");
            
                ////string Sql = " SELECT AccountOfStastic.accountID,OpName,ParentID,AccountState" +
                ////             " FROM AccountOfStastic" +
                ////             " INNER JOIN dbo.Accounts ON" +
                ////                          " AccountOfStastic.accountID = Accounts.LoginName" +
                ////             " LEFT JOIN ( SELECT DISTINCT OpID, OpName FROM  dbo.SVInfo ) svinfo ON" +
                ////                           " Accounts.LoginName  = SVInfo.opid" +
                ////             " WHERE AccountOfStastic.accountID =@LoginName AND Password = @Password"+
                ////             " AND AccountOfStastic.System = @SystemName";

                #region SqlCommand

                using (SqlCommand cmd = new SqlCommand(sqlStringBuilder.ToString(), conn))
                {
                    cmd.Parameters.Add(new SqlParameter("@LoginName", SqlDbType.NVarChar, 50));
                    cmd.Parameters["@LoginName"].Value = loginName;
                    cmd.Parameters.Add(new SqlParameter("@Password", passWord));
                    cmd.Parameters.Add(new SqlParameter("@SystemName", systemNameStr));

                    SqlDataReader reaccount = cmd.ExecuteReader();

                    while (reaccount.Read())
                    {
                        //// listLogin.Add(new Logion (){StrName=reaccount["accountID"] .ToString()});
                        login.AccountID = Common.MyIsDBNull(reaccount, "AccountID");
                        login.ParentID = Common.MyIsDBNull(reaccount, "ParentID"); 
                        login.AccountState = Common.MyIsDBNull(reaccount, "AccountState"); 
                        login.OpName = Common.MyIsDBNull(reaccount, "OpName");
                        login.RoleName = string.Empty;
                        login.Condition = string.Empty;
                        login.DataBaseName = conn.Database == string.Empty ? " " : conn.Database.ToString();
                    }

                    reaccount.Close();
                    conn.Close();
                }

                #endregion

                return login;
            }
        }
        #endregion 验证登录

        #region 返回当前用户的管理权限
        /// <summary>
        /// 返回当前用户的管理权限
        /// WCFAccounts服务用
        /// </summary>
        /// <param name="login">login 用户登录对象</param>
        /// <returns>返回 LoginDataList</returns>
        public static LoginDataList GetUserRoles(LoginDataList login) 
        {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StatisticsToolsDB"].ConnectionString))
            {
                conn.Open();

                //// string Sql = "SELECT r.RoleName,r.Condition,a.RoleID" +
                ////             " FROM" +
                ////             " Roles r, AccountRoles a" +
                ////             " WHERE r.RoleID =a.RoleID" +
                ////             " AND a.accountID= @accountID" +
                ////             " AND a.System = @SystemName";

                StringBuilder sqlStringBuilder = new StringBuilder();

                sqlStringBuilder.Append(" SELECT r.RoleName,r.Condition,a.RoleID");
                sqlStringBuilder.Append(" FROM");
                sqlStringBuilder.Append(" Roles r, AccountRoles a");
                sqlStringBuilder.Append(" WHERE r.RoleID =a.RoleID");
                sqlStringBuilder.Append(" AND a.AccountID= @AccountID");
                sqlStringBuilder.Append(" AND a.System = @SystemName");

                #region "SqlCommand"
                using (SqlCommand cmd = new SqlCommand(sqlStringBuilder.ToString(), conn))
                {
                    cmd.Parameters.Add(new SqlParameter("@AccountID", login.AccountID.ToString().Trim()));
                    cmd.Parameters.Add(new SqlParameter("@SystemName", systemNameStr));

                    SqlDataReader reaccount = cmd.ExecuteReader();

                    while (reaccount.Read())
                    {
                        login.RoleName = Common.MyIsDBNull(reaccount, "RoleName");
                        login.Condition = Common.MyIsDBNull(reaccount, "Condition");
                        login.RoleID = Common.MyIsDBNull(reaccount, "RoleID");
                    }

                    reaccount.Close();
                    conn.Close();
                }

                return login;
                #endregion
            }
        }

        #endregion 返回当前用户的管理权限

        #region 登录账号管理 显示当前可登录的信息 
        /// <summary>
        /// 显示当前可登录的信息
        /// WCFAccounts服务用
        /// DataGrid用
        /// </summary>
        /// <returns>返回 AccountManagementDataList的 List 形式 </returns>
        public static List<AccountInforBase> GetAccountManagementList()
        {
            List<AccountInforBase> lam = new List<AccountInforBase>();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StatisticsToolsDB"].ConnectionString))
            {
                conn.Open();

                ////string Sql = "SELECT DISTINCT accountID,AccountState,ParentID,OpName" +
                ////             " FROM" +
                ////             " AccountOfStastic,SVInfo" +
                ////             " WHERE" +
                ////             " AccountOfStastic.accountid = SVInfo.OpID"+
                ////             " AND AccountOfStastic.System = @SystemName";

                StringBuilder sqlStringBuilder = new StringBuilder();

                sqlStringBuilder.Append("SELECT DISTINCT AccountID,AccountState,ParentID,OpName");
                sqlStringBuilder.Append(" FROM");
                sqlStringBuilder.Append(" AccountOfStastic,SVInfo");
                sqlStringBuilder.Append(" WHERE");
                sqlStringBuilder.Append(" AccountOfStastic.accountid = SVInfo.OpID");
                sqlStringBuilder.Append(" AND AccountOfStastic.System = @SystemName");

                #region SqlCommand
                using (SqlCommand cmd = new SqlCommand(sqlStringBuilder.ToString(), conn))
                {
                    cmd.Parameters.Add(new SqlParameter("@SystemName", systemNameStr));

                    SqlDataReader reaccount = cmd.ExecuteReader();

                    while (reaccount.Read())
                    {
                        lam.Add(new AccountInforBase()
                        {
                            AccountID = Common.MyIsDBNull(reaccount, "AccountID"),
                            AccountState = Common.MyIsDBNull(reaccount, "AccountState"),
                            ParentID = Common.MyIsDBNull(reaccount, "ParentID"),
                            OpName = Common.MyIsDBNull(reaccount, "OpName")
                        });
                    }

                    reaccount.Close();
                    conn.Close();
                }

                return lam;
                #endregion
            }
        }

        #endregion 登录账号管理 显示当前可登录的信息

        #region 登录账号管理 显示所有用户的信息（可登录和不可登录）(模糊查询)
        /// <summary>
        /// 显示所有可登录的信息
        /// WCFAccounts服务用
        /// AccountManagementPage页面，在点击增加按钮时，
        /// 在弹出的窗体上的DataGrid上使用。
        /// </summary>
        /// <param name="userName">userName 用户ID</param>
        /// <returns>返回 AccountManagementDataList的List形式 </returns>
        ///  <value>
        ///  09/17/2010 EDIT SQL语句
        ///  11/17/2010 EDIT SQL语句 
        ///  </value>
        public static List<AccountInforBase> GetAccountsAllList(string userName)
        {
            List<AccountInforBase> lam = new List<AccountInforBase>();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StatisticsToolsDB"].ConnectionString))
            {
                conn.Open();

                // 09/17/2010 EDIT SQL语句
                // "SELECT DISTINCT a.LoginName,svi.opname" +
                // " FROM" +
                // " Accounts a ,SVInfo svi" +
                // " WHERE a.LoginName LIKE @UserName " +
                // " AND a.LoginName = svi.OpID";

                /// 09/17/2010 EDIT SQL语句 
                /// 查询Accounts里的所有记录，如果SVInfo表里有对应名就显示，没有则显示Unkonw
                string sql = " SELECT DISTINCT a.[LoginName],ISNULL(svi.opname,'Unkonw') [opname]" +
                            " FROM Accounts a" +
                            " LEFT JOIN SVInfo svi" +
                            " ON a.LoginName = svi.OpID" +
                            " WHERE a.LoginName LIKE @UserName ";


                #region "SqlCommand"
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@UserName", "%" + userName + "%");

                    SqlDataReader reaccount = cmd.ExecuteReader();

                    while (reaccount.Read())
                    {
                        lam.Add(new AccountInforBase()
                        {
                            AccountID = Common.MyIsDBNull(reaccount, "LoginName"),
                            OpName = Common.MyIsDBNull(reaccount, "opname"),
                            ParentID = string.Empty,
                            AccountState = string.Empty
                        });
                    }

                    reaccount.Close();
                    conn.Close();
                }

                return lam;
                #endregion
            }
        }
        #endregion 登录账号管理 显示所有用户的信息（可登录和不可登录）

        #region 登录账号管理 将指定值插入表中(设置可登录人员)
        /// <summary>
        /// 将列表中的值插入表中(设置可登录人员)
        /// WCFAccounts服务用
        /// 在AccountOfStastic表中设置可登录人员
        /// 在AccountRoles表中设置可登录人员的初始化管理权RoleID=0
        /// </summary>
        /// <param name="accountID">accountID  用户ID</param>
        /// <param name="isInputUserName">是否添加默认用户名 NoName。True:添加 False:不添加</param>
        /// <value>11/17/2010 - 11/18/2010 Edition </value>
        public static void GetInsertAccount(string accountID, bool isInputUserName)
        {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StatisticsToolsDB"].ConnectionString))
            {
                conn.Open();
                StringBuilder sql=new StringBuilder();

                // SVInfo表中 有 想要添加的记录的人名时：
                sql.Append(" INSERT INTO  [AccountOfStastic]");
                sql.Append(" ([AccountID],[AccountState],[System])" );
                sql.Append(" VALUES" ); 
                sql.Append(" (@AccountID ,1,@SystemName);");
                sql.Append(" INSERT INTO  [AccountRoles]" );
                sql.Append(" ([AccountID],[RoleID],[System])" );
                sql.Append(" VALUES" );
                sql.Append(" (@AccountID ,0,@SystemName)");

                // SVInfo表中 没有 想要添加的记录的人名时,
                // 向 SVInfo表 添加新记录人名默认值为 “NoName”
                if (isInputUserName)
                {
                    sql.Append(";");
                    sql.Append(" INSERT INTO SVInfo ([OpID],[OpName],[Unit])");
                    sql.Append(" VALUES (@AccountID,'NoName',@DBNull)");
                }

                string[] sqlString = sql.ToString().Split(';');

                SqlTransaction trans = conn.BeginTransaction();
                #region "SqlCommand"
                try
                {
                    for (int i = 0; i < sqlString.Length; i++)
                    {
                        using (SqlCommand cmd = new SqlCommand(sqlString[i], conn, trans))
                        {
                            cmd.Parameters.Add(new SqlParameter("@AccountID", SqlDbType.NVarChar, 50));
                            cmd.Parameters["@AccountID"].Value = accountID.Trim().ToString();

                            cmd.Parameters.Add(new SqlParameter("@SystemName", SqlDbType.NVarChar, 50));
                            cmd.Parameters["@SystemName"].Value = systemNameStr;

                            // SVInfo表中 没有 想要添加的记录的人名时：
                            if (i==2)
                            {
                                cmd.Parameters.Add(new SqlParameter("@DBNull", SqlDbType.NVarChar, 50));
                                cmd.Parameters["@DBNull"].Value = DBNull.Value;
                            }
                            cmd.ExecuteNonQuery();
                        }
                    }

                    trans.Commit();
                    conn.Close();
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    throw new Exception("事务操作出错，系统信息：" + ex.Message);
                }

                #endregion
            }
        }
        #endregion  登录账号管理 将指定值插入表中(设置可登录人员)

        #region 登录账号管理 删除用户
        /// <summary>
        /// 将指定值删除
        /// WCFAccounts服务用
        /// </summary>
        /// <param name="accountID"> accountID 用户ID</param>
        public static void GetDeleteAccount(string accountID)
        {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StatisticsToolsDB"].ConnectionString))
            {
                conn.Open();
                ////string Sql = "select accountID from Accounts where LoginName= @LoginName and Password= @Password";

                string sql = " DELETE FROM  [AccountOfStastic]" +
                             " WHERE" +
                             " AccountID = @AccountID AND AccountOfStastic.System = @SystemName;" +
                             " DELETE FROM  [AccountRoles]" +
                             " WHERE" +
                             " AccountID = @AccountID AND AccountRoles.System = @SystemName";

                string[] sqlString = sql.Split(';');
                SqlTransaction trans = conn.BeginTransaction();
                #region "SqlCommand"
                try
                {
                    foreach (string sqlitem in sqlString)
                    {
                        using (SqlCommand cmd = new SqlCommand(sqlitem, conn, trans))
                        {
                            cmd.Parameters.Add(new SqlParameter("@AccountID", accountID.Trim()));
                            cmd.Parameters.Add(new SqlParameter("@SystemName", systemNameStr));

                            cmd.ExecuteNonQuery();
                        }
                    }

                    trans.Commit();
                    conn.Close();
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    throw new Exception("事务操作出错，系统信息：" + ex.Message);
                }

                #endregion
            }
        }
        #endregion  登录账号管理 删除用户

        #region 得到树形列表结构的结点内容
        /// <summary>
        /// 得到树形列表结构
        /// WCFAccounts服务用
        /// </summary>
        /// <param name="conditionString">conditionString 条件</param>
        /// <param name="isAnyTree">决定是否用条件形成树 TRUE：使用条件 FALSE：不使用</param>
        /// <returns>返回 TreeViewDataList 的LIST 值</returns>
        public static List<TreeViewDataList> GetTreeViewList(string conditionString, bool isAnyTree) 
        {
            List<TreeViewDataList> treeViewList = new List<TreeViewDataList>();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StatisticsToolsDB"].ConnectionString))
            {
                conn.Open();
                string sql = string.Empty;
                if (isAnyTree == true)
                {
                    sql = String.Format("SELECT DISTINCT NodeID,ParentNodeID,NodeName,TagURI,System" +
                                        " FROM " +
                                        " RolesTreeView" +
                                        " WHERE" +
                                        " NodeID in({0}) AND RolesTreeView.System = '{1}'" +
                                        " ORDER BY NodeID", conditionString, systemNameStr);
                }
                else
                {
                    sql = String.Format(" SELECT DISTINCT NodeID,ParentNodeID,NodeName,TagURI,System" +
                                             " FROM" + 
                                             " RolesTreeView" +
                                             " WHERE" +
                                             " RolesTreeView.System = '{0}'" +
                                             " ORDER BY NodeID", systemNameStr);
                }

                #region "SqlCommand"
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    SqlDataReader reaccount = cmd.ExecuteReader();

                    while (reaccount.Read())
                    {
                        treeViewList.Add(new TreeViewDataList()
                        {
                            NodeID = Common.MyIsDBNull(reaccount, "NodeID"),
                            NodeParendID = Common.MyIsDBNull(reaccount, "ParentNodeID"),
                            NodeName = Common.MyIsDBNull(reaccount, "NodeName"),
                            TagUri = Common.MyIsDBNull(reaccount, "TagURI"),
                            System = Common.MyIsDBNull(reaccount, "System")
                        });
                    }

                    reaccount.Close();
                    conn.Close();
                }

                return treeViewList;
                #endregion
            }
        }

        #endregion 得到树形列表结构的结点内容

        #region  【权限分配】 显示所有，有登录权限的用户名
        /// <summary>
        /// 显示所有，有权限的用户名
        /// WCFAccounts服务用
        /// </summary>
        /// <returns> 返回  AccountRolesDataList的LIST值</returns>
        public static List<AccountRolesDataList> GetAccountRolesList() 
        {
            List<AccountRolesDataList> accountRolesList = new List<AccountRolesDataList>();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StatisticsToolsDB"].ConnectionString))
            {
                conn.Open();
                ////string Sql = "SELECT r.RoleID,r.RoleName,ao.accountID " +
                ////            " FROM Roles r, AccountRoles a, AccountOfStastic ao" +
                ////            " WHERE" +
                ////            " r.RoleID=a.RoleID and ao.accountID =a.accountID";

                string sql = "SELECT DISTINCT ISNULL( r.[RoleID],0) [RoleID]," +
                            " ISNULL( r.[RoleName],'Unkonw') [RoleName]," +
                            " ao.AccountID  [AccountID]," +
                            " ISNULL(svi.opname,'Unkonw') OpName" +
                            " FROM" +
                            " AccountOfStastic ao " +
                            " LEFT JOIN [AccountRoles] a ON ao.AccountID =a.AccountID" +
                            " LEFT JOIN [Roles] r ON  r.[RoleID]=a.[RoleID]" +
                            " LEFT JOIN SVInfo svi ON ao.AccountID =svi.Opid" +
                            " WHERE ao.System = @SystemName" +
                            " AND ao.AccountID !=@AccountName";

                #region "SqlCommand"
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.Parameters.Add(new SqlParameter("@SystemName", systemNameStr));
                    cmd.Parameters.Add(new SqlParameter("@AccountName", "administrator"));

                    SqlDataReader reaccount = cmd.ExecuteReader();

                    while (reaccount.Read())
                    {
                        accountRolesList.Add(new AccountRolesDataList()
                        {
                            RoleID = Common.MyIsDBNull(reaccount, "RoleID"),
                            RoleName = Common.MyIsDBNull(reaccount, "RoleName"),
                            AccountID = Common.MyIsDBNull(reaccount, "AccountID"),
                            OpName = Common.MyIsDBNull(reaccount, "OpName")
                        });
                    }

                    reaccount.Close();
                    conn.Close();
                }

                return accountRolesList;
                #endregion
            }
        }

        #endregion  【权限分配】 显示所有，有登录权限的用户名

        #region 返回现有的所有权限
        /// <summary>
        /// 返回现有的所有权限
        /// WCFAccounts服务用
        /// </summary>
        /// <returns> 返回  RolesCombboxDataList的LIST值</returns>
        public static List<RolesCombboxDataList> GetRolesList() 
        {
            List<RolesCombboxDataList> rolesList = new List<RolesCombboxDataList>();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StatisticsToolsDB"].ConnectionString))
            {
                conn.Open();

                string sql = "SELECT RoleID,RoleName FROM Roles WHERE Roles.System = @SystemName";

                #region "SqlCommand"
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.Parameters.Add(new SqlParameter("@SystemName", systemNameStr));

                    SqlDataReader reaccount = cmd.ExecuteReader();

                    while (reaccount.Read())
                    {
                        rolesList.Add(new RolesCombboxDataList()
                        {
                            RoleID = Common.MyIsDBNull(reaccount, "RoleID"),
                            RoleName = Common.MyIsDBNull(reaccount, "RoleName")
                        });
                    }

                    reaccount.Close();
                    conn.Close();
                }

                return rolesList;
                #endregion
            }
        }

        #endregion

        #region 更新用户的权限列表
        /// <summary>
        /// 更新用户的权限列表
        /// 更新一条数据
        /// WCFAccounts服务用
        /// </summary>
        /// <param name="accountID">accountID 用户ID </param>
        /// <param name="roleID">roleID 权限ID </param>
        public static void SetUpeateOneAccountRoles(string accountID, string roleID) 
        {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StatisticsToolsDB"].ConnectionString))
            {
                conn.Open();
                string sql = "UPDATE [AccountRoles] SET [AccountID] = @AccountID,[RoleID] = @RoleID" +
                            " WHERE AccountID=@AccountID" +
                            " AND AccountRoles.System = @SystemName";
    
                SqlTransaction trans = conn.BeginTransaction();
                #region "SqlCommand"
                try
                {
                    using (SqlCommand cmd = new SqlCommand(sql, conn, trans))
                    {
                        cmd.Parameters.Add(new SqlParameter("@AccountID", accountID));
                        cmd.Parameters.Add(new SqlParameter("@RoleID", roleID));
                        cmd.Parameters.Add(new SqlParameter("@SystemName", systemNameStr));

                        cmd.ExecuteNonQuery();
                    }
                   
                    trans.Commit();
                    conn.Close();
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    throw new Exception("事务操作出错，系统信息：" + ex.Message);
                }
                #endregion
            }
        }

        #endregion 更新用户的权限列表

        #region 获取客户端IP地址
        /// <summary>
        /// Gets the IP address.
        /// </summary>
        /// <returns></returns>
        public static string GetIPAddress() 
        {
            OperationContext operationContext = OperationContext.Current;
            MessageProperties messageProperties = operationContext.IncomingMessageProperties;
            RemoteEndpointMessageProperty remoteEndpointProperty = messageProperties[RemoteEndpointMessageProperty.Name] as RemoteEndpointMessageProperty;

            return string.Format("IP:{0} Port:{1}  ", remoteEndpointProperty.Address, remoteEndpointProperty.Port);

        }
        #endregion 获取客户端IP地址

        #region 对TreeView权限树 进行 增删改
        /// <summary>
        /// 对TreeView权限树 进行 增删改
        /// </summary>
        /// <param name="treeViewNodeList">The tree view node list.</param>
        /// <param name="operateType">Type of the operate.</param>
        /// <returns></returns>
        public static string GetTreeViewNode(TreeViewDataList treeViewNodeList, string operateType) 
        {
            string retString = string.Empty;
            string operationName = string.Empty;

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StatisticsToolsDB"].ConnectionString))
            {
                conn.Open();

                StringBuilder sbSql = new StringBuilder();
                
                // 判断SELECT语句
                switch (operateType.ToUpper())
                {
                    case "ADD":
                        sbSql.Append(" INSERT INTO [RolesTreeView]");
                        sbSql.Append(" ([NodeID] ,[ParentNodeID] ,[NodeName] ,[TagURI] ,[System])");
                        sbSql.Append(" VALUES");
                        sbSql.Append(" ( @NodeID, @ParentNodeID, @NodeName, @TagURI, @System )");
                        operationName = "添加 ";
                        break;
                    case "DELETE":
                        sbSql.Append(" DELETE FROM [RolesTreeView]");
                        sbSql.Append(" WHERE [NodeID] = @NodeID");
                        operationName = "删除 ";
                        break;
                    case "EDIT":
                        sbSql.Append(" UPDATE [RolesTreeView]");
                        sbSql.Append(" SET [NodeID] = @NewNodeID");
                        sbSql.Append(" ,[ParentNodeID] = @ParentNodeID");
                        sbSql.Append(" ,[NodeName] = @NodeName");
                        sbSql.Append(" ,[TagURI] = @TagURI");
                        sbSql.Append(" WHERE [NodeID] = @OldNodeID");
                        operationName = "修改 ";
                        break;
                }

                #region SqlCommand
                using (SqlCommand cmd = new SqlCommand(sbSql.ToString(), conn))
                {

                    #region 根据SELECT语句来判断输入参数
                    switch (operateType.ToUpper())
                    {
                        case "ADD":
                            //@NodeID, @ParentNodeID, @NodeName, @TagURI, @System
                            cmd.Parameters.Add(new SqlParameter("@NodeID", SqlDbType.NVarChar, 50));
                            cmd.Parameters["@NodeID"].Value = treeViewNodeList.NodeID;

                            cmd.Parameters.Add(new SqlParameter("@ParentNodeID", SqlDbType.NVarChar, 50));
                            cmd.Parameters["@ParentNodeID"].Value = treeViewNodeList.NodeParendID;

                            cmd.Parameters.Add(new SqlParameter("@NodeName", SqlDbType.NVarChar, 50));
                            cmd.Parameters["@NodeName"].Value = treeViewNodeList.NodeName;

                            cmd.Parameters.Add(new SqlParameter("@TagURI", SqlDbType.NVarChar, 500));
                            cmd.Parameters["@TagURI"].Value = treeViewNodeList.TagUri;

                            cmd.Parameters.Add(new SqlParameter("@System", SqlDbType.NVarChar, 50));
                            cmd.Parameters["@System"].Value = systemNameStr;
                            break;

                        case "DELETE":
                            //@NodeID"
                            cmd.Parameters.Add(new SqlParameter("@NodeID", SqlDbType.NVarChar, 50));
                            cmd.Parameters["@NodeID"].Value = treeViewNodeList.NodeID;
                            break;

                        case "EDIT":
                            //@NewNodeID @ParentNodeID @NodeName @TagURI @OldNodeID
                            cmd.Parameters.Add(new SqlParameter("@NewNodeID", SqlDbType.NVarChar, 50));
                            cmd.Parameters["@NewNodeID"].Value = treeViewNodeList.NodeID;

                            cmd.Parameters.Add(new SqlParameter("@ParentNodeID", SqlDbType.NVarChar, 50));
                            cmd.Parameters["@ParentNodeID"].Value = treeViewNodeList.NodeParendID;

                            cmd.Parameters.Add(new SqlParameter("@NodeName", SqlDbType.NVarChar, 50));
                            cmd.Parameters["@NodeName"].Value = treeViewNodeList.NodeName;

                            cmd.Parameters.Add(new SqlParameter("@TagURI", SqlDbType.NVarChar, 500));
                            cmd.Parameters["@TagURI"].Value = treeViewNodeList.TagUri;

                            cmd.Parameters.Add(new SqlParameter("@OldNodeID", SqlDbType.NVarChar, 50));
                            cmd.Parameters["@OldNodeID"].Value = treeViewNodeList.OldNodeID;
                            break;
                    }
                    #endregion 根据SELECT语句来判断输入参数


                    if (cmd.ExecuteNonQuery() - 1 == 0)
                    {
                        retString = operationName + "操作成功!";
                    }
                    else
                    {
                        retString = operationName + "操作失败!";
                    }
                    conn.Close();
                }
                return retString;
                #endregion
            }
        }
        #endregion 对TreeView权限树 进行 增删改

        #region 显示 所有的Roles
        /// <summary>
        /// 显示 所有的Roles
        /// </summary>
        /// <returns></returns>
        public static List<RolesCombboxDataList> GetAllRolesList()
        {
            List<RolesCombboxDataList> lam = new List<RolesCombboxDataList>();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StatisticsToolsDB"].ConnectionString))
            {
                conn.Open();
                
                StringBuilder sqlStringBuilder = new StringBuilder();

                sqlStringBuilder.Append(" SELECT [RoleID] ,[RoleName] ,[Condition]");
                sqlStringBuilder.Append(" FROM");
                sqlStringBuilder.Append(" Roles");
                sqlStringBuilder.Append(" WHERE");
                sqlStringBuilder.Append(" System = @SystemName");
                sqlStringBuilder.Append(" ORDER BY RoleID");

                #region SqlCommand
                using (SqlCommand cmd = new SqlCommand(sqlStringBuilder.ToString(), conn))
                {
                    cmd.Parameters.Add(new SqlParameter("@SystemName", systemNameStr));

                    SqlDataReader reaccount = cmd.ExecuteReader();

                    while (reaccount.Read())
                    {
                        lam.Add(new RolesCombboxDataList()
                        {
                            RoleID = Common.MyIsDBNull(reaccount, "RoleID"),
                            RoleName = Common.MyIsDBNull(reaccount, "RoleName"),
                            Condition = Common.MyIsDBNull(reaccount, "Condition")
                        });
                    }

                    reaccount.Close();
                    conn.Close();
                }

                return lam;
                #endregion
            }
        }
        #endregion 显示 所有的Roles

        #region 对权限进行 增删改
        /// <summary>
        /// 对权限进行 增删改
        /// </summary>
        /// <param name="rolesInfor">The roles infor.</param>
        /// <param name="operateType">Type of the operate.</param>
        /// <returns></returns>
        /// <returns>09/07/2010 add</returns>
        public static string SetEditRolesList(RolesCombboxDataList rolesInfor, string operateType) 
        {
            string retString = string.Empty;
            string operationName = string.Empty;

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StatisticsToolsDB"].ConnectionString))
            {
                conn.Open();

                StringBuilder sbSql = new StringBuilder();

                // 判断SELECT语句
                switch (operateType.ToUpper())
                {
                    case "ADD":
                        sbSql.Append(" INSERT INTO [Roles]");
                        sbSql.Append(" ([RoleID] ,[RoleName] ,[Condition] ,[System])");
                        sbSql.Append(" VALUES");
                        sbSql.Append(" ( @RoleID, @RoleName, @Condition, @System )");
                        operationName = "添加 ";
                        break;
                    case "DELETE":
                        sbSql.Append(" DELETE FROM [Roles]");
                        sbSql.Append(" WHERE [RoleName] = @RoleName");
                        operationName = "删除 ";
                        break;
                    case "EDIT":
                        sbSql.Append(" UPDATE [Roles]");
                        sbSql.Append(" SET [RoleID] = @NewRoles");
                        sbSql.Append(" ,[RoleName] = @RoleName");
                        sbSql.Append(" ,[Condition] = @Condition");
                        sbSql.Append(" WHERE [RoleName] = @OldRoleName");
                        operationName = "修改 ";
                        break;
                }

                #region SqlCommand
                using (SqlCommand cmd = new SqlCommand(sbSql.ToString(), conn))
                {

                    #region 根据SELECT语句来判断输入参数
                    switch (operateType.ToUpper())
                    {
                        case "ADD":
                            //@RoleID, @RoleName, @Condition, @System
                            cmd.Parameters.Add(new SqlParameter("@RoleID", SqlDbType.NVarChar, 50));
                            cmd.Parameters["@RoleID"].Value = rolesInfor.RoleID;

                            cmd.Parameters.Add(new SqlParameter("@RoleName", SqlDbType.NVarChar, 50));
                            cmd.Parameters["@RoleName"].Value = rolesInfor.RoleName;

                            cmd.Parameters.Add(new SqlParameter("@Condition", SqlDbType.NVarChar, 500));
                            cmd.Parameters["@Condition"].Value = rolesInfor.Condition;

                            cmd.Parameters.Add(new SqlParameter("@System", SqlDbType.NVarChar, 50));
                            cmd.Parameters["@System"].Value = systemNameStr;
                            break;

                        case "DELETE":
                            //@RoleName
                            cmd.Parameters.Add(new SqlParameter("@RoleName", SqlDbType.NVarChar, 50));
                            cmd.Parameters["@RoleName"].Value = rolesInfor.RoleName;
                            break;

                        case "EDIT":
                            //@NewRoles @RoleName @Condition @OldRoleName
                            cmd.Parameters.Add(new SqlParameter("@NewRoles", SqlDbType.NVarChar, 50));
                            cmd.Parameters["@NewRoles"].Value = rolesInfor.RoleID;

                            cmd.Parameters.Add(new SqlParameter("@RoleName", SqlDbType.NVarChar, 50));
                            cmd.Parameters["@RoleName"].Value = rolesInfor.RoleName;

                            cmd.Parameters.Add(new SqlParameter("@Condition", SqlDbType.NVarChar, 500));
                            cmd.Parameters["@Condition"].Value = rolesInfor.Condition;

                            cmd.Parameters.Add(new SqlParameter("@OldRoleName", SqlDbType.NVarChar, 50));
                            cmd.Parameters["@OldRoleName"].Value = rolesInfor.OldRoleName;
                            break;
                    }
                    #endregion 根据SELECT语句来判断输入参数

                     
                    if (cmd.ExecuteNonQuery() - 1 == 0)
                    {
                        retString = operationName + "操作成功!";
                    }
                    else
                    {
                        retString = operationName + "操作失败!";
                    }
                    conn.Close();
                }
                return retString;
                #endregion
            }
        }

        #endregion  对权限进行 增删改

        #region 【新增用户子窗体】 查询已有用户
        /// <summary>
        /// 查询已有用户
        /// </summary>
        /// <returns></returns>
        /// <value>09/19/2010 Add</value>
        public static List<AccountData> GetAccountInforList()
        {
            List<AccountData> lam = new List<AccountData>();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StatisticsToolsDB"].ConnectionString))
            {
                conn.Open();

                StringBuilder sqlStringBuilder = new StringBuilder();

                sqlStringBuilder.Append(" SELECT [AccountID],[LoginName],[Password]");
                sqlStringBuilder.Append(" FROM [Accounts]");
                sqlStringBuilder.Append(" ORDER BY AccountID DESC");
                
                #region SqlCommand
                using (SqlCommand cmd = new SqlCommand(sqlStringBuilder.ToString(), conn))
                {

                    SqlDataReader reaccount = cmd.ExecuteReader();

                    while (reaccount.Read())
                    {
                        lam.Add(new AccountData()
                        {
                            Id =Convert .ToInt32( reaccount["AccountID"].ToString()),//自增ID
                            AccountID = Common.MyIsDBNull(reaccount, "LoginName"),//用户登录ID
                            PassWord = Common.MyIsDBNull(reaccount, "Password")
                        });
                    }

                    reaccount.Close();
                    conn.Close();
                }

                return lam;
                #endregion
            }
        }
        #endregion 【新增用户子窗体】 查询已有用户

        #region 【新增用户子窗体】 添加新增用户
        /// <summary>
        /// 添加新增用户
        /// </summary>
        /// <returns></returns>
        /// <value>09/20/2010 Add</value>
        public static string SetInsertNewAccount(List<AccountData> accountList)
        {
            string retStr = "操作成功！";
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StatisticsToolsDB"].ConnectionString))
            {
                conn.Open();

                /*
                 * Account 
                 * 表字段    List<AccountData>字段
                 * AccountID    ID
                 * LoginName    AccountID
                 * Password     password
                 * 
                 * SvInfor 
                 * OpID         AccountID
                 * OpName       OpName
                 * Unit         null
                 */

                StringBuilder sqlStringBuilder = new StringBuilder();

                sqlStringBuilder.Append(" INSERT INTO [SVInfo]");
                sqlStringBuilder.Append(" ([OpID],[OpName],[Unit])");
                sqlStringBuilder.Append(" VALUES");
                sqlStringBuilder.Append(" (@AccountID,@OpName,@Unit);");

                sqlStringBuilder.Append(" INSERT INTO [Accounts]");
                sqlStringBuilder.Append(" ([AccountID],[LoginName],[Password])");
                sqlStringBuilder.Append(" VALUES");
                sqlStringBuilder.Append(" (@ID,@AccountID,@Password)");


                string[] sqlString = sqlStringBuilder.ToString().Split(';');
                SqlTransaction trans = conn.BeginTransaction();

                #region SqlCommand

                try
                {
                    for (int i = 0; i < accountList.Count; i++)
                    {
                        foreach (string sqlitem in sqlString)
                        {
                            #region using
                            using (SqlCommand cmd = new SqlCommand(sqlitem, conn, trans))
                            {
                                cmd.Parameters.Add(new SqlParameter("@AccountID", SqlDbType.NVarChar, 50));
                                cmd.Parameters["@AccountID"].Value = accountList[i].AccountID;

                                cmd.Parameters.Add(new SqlParameter("@OpName", SqlDbType.NVarChar, 50));
                                cmd.Parameters["@OpName"].Value = accountList[i].OpName;

                                cmd.Parameters.Add(new SqlParameter("@Unit", SqlDbType.NVarChar, 50));
                                cmd.Parameters["@Unit"].Value = DBNull.Value;

                                cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int));
                                cmd.Parameters["@ID"].Value = accountList[i].Id;

                                cmd.Parameters.Add(new SqlParameter("@Password", SqlDbType.NVarChar, 50));
                                cmd.Parameters["@Password"].Value = accountList[i].PassWord;

                                cmd.ExecuteNonQuery();

                            }
                            #endregion using
                        }
                    }
                    trans.Commit();
                    conn.Close();
                }
                catch (Exception ex) 
                {
                    trans.Rollback();
                    retStr = "操作失败！";
                    throw new Exception("事务操作出错，系统信息：" + ex.Message);
                }
                return retStr;
                
                #endregion
            }
        }
        #endregion  【新增用户子窗体】 添加新增用户

        #region 【SV管理页面】

        /// <summary>
        /// 查找已/未分组的SV或SSV信息
        /// </summary>
        /// <param name="id">用户ID（可模糊查询。值空：空值查询所有）</param>
        /// <param name="isGroup">true: 已分组 False: 末分组</param>
        /// <returns>已/未分组的SV或SSV信息</returns>
        /// <value>10/25/2010 add</value>
        public static List<AccountInforBase> GetGroupUnitList(string id, bool isGroup) 
        {
            List<AccountInforBase> retList = new List<AccountInforBase>();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StatisticsToolsDB"].ConnectionString))
            {
                conn.Open();

                string sql = " SELECT DISTINCT [OpID] ,[OpName]" +
                             " FROM [SVInfo],[AccountRoles],[Roles]";

                string isGroupStr=string.Empty ;
                if (isGroup)
                {   //已分组
                    isGroupStr = " WHERE Unit is not null AND unit !=''";
                }
                else
                {
                    //末分组
                    isGroupStr = " WHERE ( Unit is null OR unit ='' ) ";
                }

                sql += isGroupStr +
                    " AND OpID like @UserId" +
                    " ORDER BY [OpName]";
                                                         

                #region "SqlCommand"

                // 用返回值的第一条记录来判断返回值是已/末分组
                if (isGroup)
                {   //已分组
                    retList.Add(new AccountInforBase()
                    {
                        AccountID ="TRUE"
                    });
                }
                else
                {
                    //末分组
                    retList.Add(new AccountInforBase()
                    {
                        AccountID = "FALSE"
                    });
                }

                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@UserId", "%" + id + "%");

                    SqlDataReader reaccount = cmd.ExecuteReader();
                    
                    while (reaccount.Read())
                    {
                        retList.Add(new AccountInforBase()
                        {
                            AccountID = Common.MyIsDBNull(reaccount, "OpID"),
                            OpName = Common.MyIsDBNull(reaccount, "OpName"),
                            //用ParentID来记录是否是末/已分组用户
                            ParentID = isGroup.ToString()
                        });
                    }

                    reaccount.Close();
                    conn.Close();
                }

                return retList;
                #endregion
            }
        }

        /// <summary>
        /// 显示SV的详细 分组 信息
        /// </summary>
        /// <param name="id">用户ID.</param>
        /// <returns></returns>
        /// <value>10/25/2010 add</value>
        public static List<AccountData> GetSvDetailedInfoList(string id)
        {
            List<AccountData> retList = new List<AccountData>();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StatisticsToolsDB"].ConnectionString))
            {
                conn.Open();

                string sql = " SELECT [OpID] ,[OpName],[Unit]" +
                             " FROM [SVInfo]" +
                             " WHERE OpID =@UserID ORDER BY [Unit]";


                #region "SqlCommand"
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.Parameters.Add(new SqlParameter("@UserID", SqlDbType.NVarChar));
                    cmd.Parameters["@UserID"].Value = id;

                    SqlDataReader reaccount = cmd.ExecuteReader();

                    while (reaccount.Read())
                    {
                        retList.Add(new AccountData()
                        {
                            AccountID = Common.MyIsDBNull(reaccount, "OpID"),
                            OpName = Common.MyIsDBNull(reaccount, "OpName"),
                            // 用AccountState存 Unit信息。
                            AccountState = Common.MyIsDBNull(reaccount, "Unit")
                        });
                    }

                    reaccount.Close();
                    conn.Close();
                }

                return retList;
                #endregion
            }
        }

        /// <summary>
        /// 添加组信息
        /// </summary>
        /// <param name="userId">用户ID</param>
        /// <param name="userName">用户姓名</param>
        /// <param name="gruopName">添加的组名</param>
        /// <param name="isGrouped">是否已分组 true:已分组  false: 没分组</param>
        /// <returns></returns>
        /// <value>10/28/2010 add</value>
        public static bool GetSvGroupAdd(string userId, string userName, string gruopName, bool isGrouped)
        {
            //操作成功
            bool retValue = true;
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StatisticsToolsDB"].ConnectionString))
            {
                conn.Open();

                StringBuilder sql;

                if (!isGrouped)
                {
                    ///是新用户，没分组
                    ///所以更新当前记录的组信息
                    sql = new StringBuilder();
                    sql.Append(" UPDATE [SVInfo]");
                    sql.Append(" SET");
                    sql.Append(" [Unit] = @GruopName");
                    sql.Append(" WHERE OpID= @UserID");
                }
                else
                {
                    ///已经有分组信息的旧用户
                    ///再添加分组用INSRT
                    sql = new StringBuilder();
                    sql.Append(" INSERT INTO [SVInfo]");
                    sql.Append(" ([OpID] ,[OpName] ,[Unit])");
                    sql.Append(" VALUES( ");
                    sql.Append(" @UserID");
                    sql.Append(" ,@UserName");
                    sql.Append(" ,@GruopName )");
                }


                #region "SqlCommand"
                using (SqlCommand cmd = new SqlCommand(sql.ToString(), conn))
                {
                    if (isGrouped)
                    {
                        cmd.Parameters.Add(new SqlParameter("@UserName", SqlDbType.NVarChar));
                        cmd.Parameters["@UserName"].Value = userName;
                    }

                    cmd.Parameters.Add(new SqlParameter("@UserID", SqlDbType.NVarChar));
                    cmd.Parameters["@UserID"].Value = userId;

                    cmd.Parameters.Add(new SqlParameter("@GruopName", SqlDbType.NVarChar));
                    cmd.Parameters["@GruopName"].Value = gruopName;


                    if (cmd.ExecuteNonQuery() - 1 != 0)
                    {
                        //操作失败
                        retValue = false;
                    }
                    conn.Close();

                }

                return retValue;
                #endregion
            }
        }

        /// <summary>
        /// 删除组信息
        /// </summary>
        /// <param name="userId">用户ID</param>
        /// <param name="userName">用户姓名</param>
        /// <param name="gruopName">要删除的组名</param>
        /// <param name="remainsOfRecords">当前用户剩余的组数</param>
        /// <returns></returns>
        /// <value>10/29/2010 add</value>
        public static bool GetSvGroupDel(string userId, string userName, string gruopName,int remainsOfRecords) 
        {
            //操作成功
            bool retValue = true;

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StatisticsToolsDB"].ConnectionString))
            {
                conn.Open();

                StringBuilder sql;

                if (remainsOfRecords > 1)
                {
                    ///此用户有多个分组
                    ///所以删除当前分组
                    sql = new StringBuilder();
                    sql.Append(" DELETE FROM [SVInfo]");
                    sql.Append(" WHERE ");
                    sql.Append(" [OpID]= @UserID");
                    sql.Append(" AND [OpName]=@UserName");
                    sql.Append(" AND [Unit]= @GruopName");
                }
                else
                {
                    //remainsOfRecords =1
                    ///此用户只有一个分组
                    ///所以更新当前分组为NULL
                    sql = new StringBuilder();
                    sql.Append(" UPDATE [SVInfo]");
                    sql.Append(" SET [Unit] =@NullUnit");
                    sql.Append(" WHERE ");
                    sql.Append(" [OpID] =@UserID");
                    sql.Append(" AND [OpName] =@UserName");
                }


                #region "SqlCommand"
                using (SqlCommand cmd = new SqlCommand(sql.ToString(), conn))
                {
                    if (remainsOfRecords > 1)
                    {
                        cmd.Parameters.Add(new SqlParameter("@GruopName", SqlDbType.NVarChar));
                        cmd.Parameters["@GruopName"].Value = gruopName;
                    }
                    else
                    {
                        cmd.Parameters.Add(new SqlParameter("@NullUnit", SqlDbType.NVarChar));
                        cmd.Parameters["@NullUnit"].Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(new SqlParameter("@UserID", SqlDbType.NVarChar));
                    cmd.Parameters["@UserID"].Value = userId;

                    cmd.Parameters.Add(new SqlParameter("@UserName", SqlDbType.NVarChar));
                    cmd.Parameters["@UserName"].Value = userName;


                    if (cmd.ExecuteNonQuery() - 1 < 0)
                    {
                        //操作失败
                        retValue = false;
                    }
                    conn.Close();
                }

                return retValue;
                #endregion
            }

        }

        /// <summary>
        /// 要删除的组记录总数
        /// </summary>
        /// <param name="userId">用户ID</param>
        /// <param name="userName">用户姓名</param>
        /// <returns></returns>
        /// <value>10/29/2010 add</value>
        public static int GetRemainsOfRecordNumber(string userId, string userName)
        {
            int retValue = 0;
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StatisticsToolsDB"].ConnectionString))
            {
                conn.Open();

                StringBuilder sql = new StringBuilder();

                sql.Append(" SELECT COUNT([OpID])");
                sql.Append(" FROM [SVInfo]");
                sql.Append(" WHERE");
                sql.Append(" [OpID] =@UserID AND [OpName]=@UserName");


                #region "SqlCommand"
                using (SqlCommand cmd = new SqlCommand(sql.ToString(), conn))
                {
                    cmd.Parameters.Add(new SqlParameter("@UserID", SqlDbType.NVarChar));
                    cmd.Parameters["@UserID"].Value = userId;

                    cmd.Parameters.Add(new SqlParameter("@UserName", SqlDbType.NVarChar));
                    cmd.Parameters["@UserName"].Value = userName;

                    SqlDataReader reaccount = cmd.ExecuteReader();

                    while (reaccount.Read())
                    {
                        retValue = Convert.ToInt32(reaccount[0].ToString());
                    }
                    conn.Close();
                }

                return retValue;
                #endregion
            }
        }

        /// <summary>
        /// 修改当前用户的真实名字
        /// </summary>
        /// <param name="userId">用户ID.</param>
        /// <param name="userName">用户姓名</param>
        /// <returns></returns>
        /// <value>11/18/2010 Add </value>
        public static bool GetReName(string userId, string userName)
        {
            bool retbool = true;

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StatisticsToolsDB"].ConnectionString))
            {
                conn.Open();

                StringBuilder sql = new StringBuilder();

                sql.Append(" UPDATE [SVInfo]");
                sql.Append(" SET [OpName] = @userName");
                sql.Append(" WHERE");
                sql.Append(" [OpID] =@UserID");


                #region "SqlCommand"
                using (SqlCommand cmd = new SqlCommand(sql.ToString(), conn))
                {

                    cmd.Parameters.Add(new SqlParameter("@UserID", SqlDbType.NVarChar));
                    cmd.Parameters["@UserID"].Value = userId;

                    cmd.Parameters.Add(new SqlParameter("@userName", SqlDbType.NVarChar));
                    cmd.Parameters["@userName"].Value = userName;

                    if (cmd.ExecuteNonQuery() - 1 < 0)
                    {
                        //操作失败
                        retbool = false;
                    }
                    conn.Close();

                }

                return retbool;
                #endregion
            }
        }


        #endregion 【SV管理页面】
      
    }
}
